package com.design.working.dao.impl;

import com.design.working.dao.LoginDAO;
import com.design.working.dto.LoginDTO;
import com.design.working.entity.Admin;
import com.design.working.entity.Counselor;
import com.design.working.entity.DeptEmployee;
import com.design.working.entity.Student;
import com.design.working.util.JDBCUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * @author gavin
 * @date 2022/5/7 20:59
 */
public class LoginDAOImpl implements LoginDAO {

    /**
     * 学生登录
     */
    @Override
    public Student studentLogin(LoginDTO loginDTO) throws SQLException {
        Student student = null;
        Connection connection = JDBCUtil.getConnection();
        PreparedStatement preparedStatement;
        String sql = "SELECT uid, password, username FROM student " +
                     "WHERE uid = ? AND password = ? AND is_deleted = 0;";
        ResultSet resultSet;

        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1, loginDTO.getUid());
        preparedStatement.setString(2, loginDTO.getPassword());
        resultSet = preparedStatement.executeQuery();

        if(resultSet.next()) {
            student = new Student(resultSet.getInt(1),
                                  resultSet.getString(2),
                                  resultSet.getString(3));
        }
        JDBCUtil.close(resultSet, preparedStatement, connection);
        return student;
    }

    /**
     * 辅导员登录
     */
    @Override
    public Counselor counselorLogin(LoginDTO loginDTO) throws SQLException {

        Counselor counselor = null;
        Connection connection = JDBCUtil.getConnection();
        PreparedStatement preparedStatement;
        String sql = "SELECT uid, username, password FROM counselor " +
                     "WHERE uid = ? AND password = ? AND is_deleted = 0;";
        ResultSet resultSet;

        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1, loginDTO.getUid());
        preparedStatement.setString(2, loginDTO.getPassword());
        resultSet = preparedStatement.executeQuery();

        if(resultSet.next()) {
            counselor = new Counselor(resultSet.getInt(1),
                                      resultSet.getString(2),
                                      resultSet.getString(3));
        }

        JDBCUtil.close(resultSet, preparedStatement, connection);
        return counselor;
    }

    /**
     * 用工部门员工登录
     */
    @Override
    public DeptEmployee deptEmployeeLogin(LoginDTO loginDTO) throws SQLException {
        DeptEmployee deptEmployee = null;
        Connection connection = JDBCUtil.getConnection();
        PreparedStatement preparedStatement;
        String sql = "SELECT uid, username, password FROM dept_employee " +
                     "WHERE uid = ? AND password = ? AND is_deleted = 0;";
        ResultSet resultSet;

        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1, loginDTO.getUid());
        preparedStatement.setString(2, loginDTO.getPassword());
        resultSet = preparedStatement.executeQuery();

        if(resultSet.next()) {
            deptEmployee = new DeptEmployee(resultSet.getInt(1),
                                            resultSet.getString(2),
                                            resultSet.getString(3));
        }
        JDBCUtil.close(resultSet, preparedStatement, connection);
        return deptEmployee;
    }

    /**
     * 管理员登录
     */
    @Override
    public Admin adminLogin(LoginDTO loginDTO) throws SQLException {
        Admin admin = null;
        Connection connection = JDBCUtil.getConnection();
        PreparedStatement preparedStatement;
        String sql = "SELECT uid, username, password FROM admin WHERE uid = ? AND password = ? AND is_deleted = 0;";
        ResultSet resultSet;

        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1, loginDTO.getUid());
        preparedStatement.setString(2, loginDTO.getPassword());
        resultSet = preparedStatement.executeQuery();

        if(resultSet.next()) {
            admin = new Admin(resultSet.getInt(1),
                              resultSet.getString(2),
                              resultSet.getString(3));
        }
        JDBCUtil.close(resultSet, preparedStatement, connection);
        return admin;
    }
}